<?php

class CCrawl extends Zend_Db_Table
{

    protected $_name;

    protected $_primary;

    protected $typeTable=null;

    public function _setup()
    {

        $this->_name=R_PRE."crawl_item";

        $this->_primary="id";

        parent::_setup();

    }
	
	public function getAll($sql){
		
		$db=$this->getAdapter();
		
		return $db->fetchAll($sql);
		//return $db->fetchAll("SELECT * from ".$this->_name." where cutUrl='' limit 100");
		
	}
	
	public function getAllRecords($sql){
		
		$db=$this->getAdapter();
		
		$select=$db->select();
		
		$select->from($this->_name,"*");
		
		return $db->fetchAll($sql);
		
	}
	
	public function updateRecord($itemid,$data)
    {
        $db=$this->getAdapter();
        $where = $db->quoteInto('id = ?', $itemid);
        $rows_affected=$db->update($this->_name,$data,$where);
        if($rows_affected!=1)
        {
            return false;
        }else
        {
            return true;
        }
    }
	
	public function getPageCount($pageNum)
	{
		
		$sql = 'select count(*) from ' . $this->_name;
		
		$count = $this->getAdapter()->fetchOne($sql);
		
		return ceil($count / $pageNum);
		
	}
	
	public function getAllCrawls($page, $pageNum)
	{
		$sql = 'select * from ' . $this->_name;
		
		$offset = ($page - 1) * $pageNum;
		
		$sql .= ' order by id asc limit ' . $offset . ',' . $pageNum;
	
		return  $this->getAdapter()->fetchAll($sql);
	}
	
	public function getRssCrawls($limit = 1500)
	{
		$sql = 'select * from ' . $this->_name;
		$sql .= ' order by id desc limit ' . $limit;
	
		return  $this->getAdapter()->fetchAll($sql);
	}
	
	public function getRssCrawlsOffset($limit,$offset)
	{
		$sql = 'select * from ' . $this->_name;
		$sql .= ' order by id asc limit '.$offset . ',' . $limit;
		return  $this->getAdapter()->fetchAll($sql);
	}
	
	public function update($sql){
		$db=$this->getAdapter();
		$db->query($sql);
	}
	
	public function getCrawlItem($id){
		$db=$this->getAdapter();
		$crawlItem=$db->fetchAll("SELECT * from ".$this->_name." WHERE id=$id");		
		return $crawlItem[0];
	}
	
	public function getCrawlItems($idArray){
		$idStr = implode(",",$idArray);
		$db=$this->getAdapter();
		$crawlItemArray=$db->fetchAll("SELECT * from ".$this->_name." WHERE id IN ($idStr)");		
		return $crawlItemArray;
	}
	
	public function getMaxId(){
		$db=$this->getAdapter();
		return $db->fetchOne("SELECT MAX(Id) from ".$this->_name);		
	}
	
	public function renameCrawl(){

		$db=$this->getAdapter();
		$db_adapter = Zend_Db::factory('PDO_MYSQL',
				array(
					'host'				=> 'localhost',
					'username'          => 'admin',
					'password'          => 'qa1234',
					'dbname'            => 'newjerseysoccer',
		));
		/*
		$newjerseysoccerItemArray = $db_adapter->fetchAll("SELECT * from ".$this->_name);
		
		foreach($newjerseysoccerItemArray as $item){
			$db->update ($this->_name,array ('name'=> $item['name']),$this->_db->quoteInto ('id=?', $item['id']));
		}
		return;*/
		
		$keywordsArray=$db_adapter->fetchAll("SELECT keywords from ".R_PRE."keywords");	
		$nameArray = array();
		foreach($keywordsArray as $key){
			$karray = explode(' ',$key['keywords']);
			foreach($karray as $kw){
				$kw = strtolower(trim($kw));
				if(preg_match("/^[a-z]+$/",$kw) && $kw !='soccer' && $kw !='jersey' && $kw !='shirt' && $kw !='home' && $kw !='long' && $kw!='away' && strlen($kw)>3){
					array_push($nameArray,$kw);
				}
			}
		}
		$nameArray = array_unique($nameArray);
		echo count($nameArray).'<br />';
		$db=$this->getAdapter();
		$crawlItemArray=$db->fetchAll("SELECT * from ".$this->_name);	
		echo count($crawlItemArray).'<br />';
		foreach($crawlItemArray as $item){
			$narray = explode(' ',$item['name']);
			$titleKey = array();
			foreach($narray as $k){
				foreach($nameArray as $nam){
					if(strtolower(trim($k)) == strtolower(trim($nam))){
						array_push($titleKey,trim($k));
					}
				}
			}
			$titleKey = array_unique($titleKey);
			echo count($titleKey).'<br />';
			if(count($titleKey)>0){
				$titlekeyStr = implode(' ',$titleKey);
				$db->update ($this->_name,array ('name'=> $item['name'].'('.$titlekeyStr.')'),$this->_db->quoteInto ('id=?', $item['id']));
				echo $item['name'].'('.$titlekeyStr.')';
			}
		}
	}

	
	
	
}